//    Copyright (C) 2013  <lugocreation@gmail.com>
//
//    This program is free software: you can redistribute it and/or modify
//    it under the terms of the GNU General Public License as published by
//    the Free Software Foundation, either version 3 of the License, or
//    (at your option) any later version.
//
//    This program is distributed in the hope that it will be useful,
//    but WITHOUT ANY WARRANTY; without even the implied warranty of
//    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
//    GNU General Public License for more details.
//
//    You should have received a copy of the GNU General Public License
//    along with this program.  If not, see <http://www.gnu.org/licenses/>.
package org.jpgolay.me.item;

import java.sql.*;
import java.util.Vector;
import org.jpgolay.me.persistence.OLD_RequestDB;

public class OLD_ItemList {

    public OLD_ItemList() {
        resultat = false;
        dbr = new OLD_RequestDB();
    }

    public boolean searchBy(int select) {
        switch (select) {
            case 1: // '\001'
                selectByReferences();
                break;

            case 2: // '\002'
                selectByDesignations();
                break;

            case 3: // '\003'
                selectPromotions();
                break;

            case 4: // '\004'
                selectAlertes();
                break;

            case 16: // '\020'
                selectByFamilles();
                break;

            case 17: // '\021'
                selectByFournisseurs();
                break;

            case 18: // '\022'
                selectByEmplacement();
                break;

            case 19: // '\023'
                selectByWeb();
                break;

            case 20: // '\024'
                selectForFicheD();
                break;

            case 22: // '\026'
                selectForFicheCA();
                break;
        }
        return resultat;
    }

    public boolean searchById(int select, String id) {
        switch (select) {
            case 5: // '\005'
                selectFamillesId(id);
                break;

            case 6: // '\006'
                selectFournisseursId(id);
                break;
        }
        return resultat;
    }

    public boolean searchBy(int select, String like) {
        switch (select) {
            case 8: // '\b'
                selectLikeReferences(like);
                break;

            case 9: // '\t'
                selectLikeDesignations(like);
                break;

            case 10: // '\n'
                selectLikeWeb(like);
                break;

            case 11: // '\013'
                selectLikeEmplacement(like);
                break;

            case 21: // '\025'
                selectLikeForFicheD(like);
                break;

            case 23: // '\027'
                selectLikeForFicheCA(like);
                break;
        }
        return resultat;
    }

    public boolean searchBy(int select, double valeur) {
        switch (select) {
            case 12: // '\f'
                selectPrixVentePPQ(valeur);
                break;

            case 13: // '\r'
                selectPrixVentePGQ(valeur);
                break;

            case 14: // '\016'
                selectMargePPQ(valeur);
                break;

            case 15: // '\017'
                selectMargePGQ(valeur);
                break;
        }
        return resultat;
    }

    public Vector getRangees() {
        return resultatsRangees;
    }

    public Vector getColonnes() {
        return resultatsColonnes;
    }

    private void selectByReferences() {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION, price_list2 as TGweb, prix_revient as PR, price_list1 as PV, format((( price_list1 - prix_revient)/( price_list1/100)), 2 ) as MARGE, tva_id as TVA, promotion as PROMO from articles order by ref_code_barre";
        getResultat8Lines(requete);
    }

    private void selectByDesignations() {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION, price_list2 as TGweb, prix_revient as PR, price_list1 as PV, format((( price_list1 - prix_revient)/( price_list1/100)), 2 ) as MARGE, tva_id as TVA, promotion as PROMO from articles order by designation";
        getResultat8Lines(requete);
    }

    private void selectForFicheD() {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION from articles order by designation";
        getResultat2Lines(requete);
    }

    private void selectForFicheCA() {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION from articles order by REFERENCE";
        getResultat2Lines(requete);
    }

    private void selectLikeForFicheD(String like) {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION from articles where designation like '" + like + "%' " + "order by designation";
        getResultat2Lines(requete);
    }

    private void selectLikeForFicheCA(String like) {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION from articles where ref_code_barre like '" + like + "%' " + " order by REFERENCE";
        getResultat2Lines(requete);
    }

    private void selectByEmplacement() {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION, price_list2 as TGweb, prix_revient as PR, price_list1 as PV, format((( price_list1 - prix_revient)/( price_list1/100)), 2 ) as MARGE, tva_id as TVA, promotion as PROMO from articles order by STOCK";
        getResultat8Lines(requete);
    }

    private void selectByWeb() {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION, price_list2 as TGweb, prix_revient as PR, price_list1 as PV, format((( price_list1 - prix_revient)/( price_list1/100)), 2 ) as MARGE, tva_id as TVA, promotion as PROMO from articles order by WEB";
        getResultat8Lines(requete);
    }

    private void selectByFamilles() {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION, price_list2 as TGweb, prix_revient as PR, price_list1 as PV, format((( price_list1 - prix_revient)/( price_list1/100)), 2 ) as MARGE, tva_id as TVA, promotion as PROMO from articles order by familles_articles_id, designation";
        getResultat8Lines(requete);
    }

    private void selectByFournisseurs() {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION, price_list2 as TGweb, prix_revient as PR, price_list1 as PV, format((( price_list1 - prix_revient)/( price_list1/100)), 2 ) as MARGE, tva_id as TVA, promotion as PROMO from articles order by fournisseurs_id, designation";
        getResultat8Lines(requete);
    }

    private void selectPromotions() {
        resultatsRangees = new Vector();
        resultatsColonnes = new Vector();
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION, price_list2 as TGweb, prix_revient as PR, price_list1 as PV, format((( price_list1 - prix_revient)/( price_list1/100)), 2 ) as MARGE, tva_id as TVA, promotion as PROMO from articles where promotion != 0 order by designation";
        getResultat8Lines(requete);
    }

    private void selectAlertes() {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION, price_list2 as TGweb, prix_revient as PR, price_list1 as PV, format((( price_list1 - prix_revient)/( price_list1/100)), 2 ) as MARGE, tva_id as TVA, promotion as PROMO from articles order by ALERTE";
        getResultat8Lines(requete);
    }

    private void selectFamillesId(String id) {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION, price_list2 as TGweb, prix_revient as PR, price_list1 as PV, format((( price_list1 - prix_revient)/( price_list1/100)), 2 ) as MARGE, tva_id as TVA, promotion as PROMO from articles where familles_articles_id='" + id + "' order by designation";
        getResultat8Lines(requete);
    }

    private void selectFournisseursId(String id) {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION, price_list2 as TGweb, prix_revient as PR, price_list1 as PV, format((( price_list1 - prix_revient)/( price_list1/100)), 2 ) as MARGE, tva_id as TVA, promotion as PROMO from articles where fournisseurs_id='" + id + "' order by designation";
        getResultat8Lines(requete);
    }

    private void selectLikeReferences(String like) {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION, price_list2 as TGweb, prix_revient as PR, price_list1 as PV, format((( price_list1 - prix_revient)/( price_list1/100)), 2 ) as MARGE, tva_id as TVA, promotion as PROMO from articles where ref_code_barre like '" + like + "%' " + "order by ref_code_barre";
        getResultat8Lines(requete);
    }

    private void selectLikeDesignations(String like) {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION, price_list2 as TGweb, prix_revient as PR, price_list1 as PV, format((( price_list1 - prix_revient)/( price_list1/100)), 2 ) as MARGE, tva_id as TVA, promotion as PROMO from articles where designation like '" + like + "%' " + "order by designation";
        getResultat8Lines(requete);
    }

    private void selectLikeWeb(String like) {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION, price_list2 as TGweb, prix_revient as PR, price_list1 as PV, format((( price_list1 - prix_revient)/( price_list1/100)), 2 ) as MARGE, tva_id as TVA, promotion as PROMO from articles where web like '" + like + "%' " + "order by ref_code_barre";
        getResultat8Lines(requete);
    }

    private void selectLikeEmplacement(String like) {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION, price_list2 as TGweb, prix_revient as PR, price_list1 as PV, format((( price_list1 - prix_revient)/( price_list1/100)), 2 ) as MARGE, tva_id as TVA, promotion as PROMO from articles where emplacement like '" + like + "%' " + "order by ref_code_barre";
        getResultat8Lines(requete);
    }

    private void selectPrixVentePPQ(double valeur) {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION, price_list2 as TGweb, prix_revient as PR, price_list1 as PV, format((( price_list1 - prix_revient)/( price_list1/100)), 2 ) as MARGE, tva_id as TVA, promotion as PROMO from articles where price_list1 < '" + valeur + "' order by ref_code_barre";
        getResultat8Lines(requete);
    }

    private void selectPrixVentePGQ(double valeur) {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION, price_list2 as TGweb, prix_revient as PR, price_list1 as PV, format((( price_list1 - prix_revient)/( price_list1/100)), 2 ) as MARGE, tva_id as TVA, promotion as PROMO from articles where price_list1 > '" + valeur + "' order by ref_code_barre";
        getResultat8Lines(requete);
    }

    private void selectMargePPQ(double valeur) {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION, price_list2 as TGweb, prix_revient as PR, price_list1 as PV, format((( price_list1 - prix_revient)/( price_list1/100)), 2 ) as MARGE, tva_id as TVA, promotion as PROMO from articles where format((( price_list1 - prix_revient)/( price_list1/100)), 2 ) < '" + valeur + "' order by ref_code_barre";
        getResultat8Lines(requete);
    }

    private void selectMargePGQ(double valeur) {
        String requete = "select ref_code_barre as REFERENCE, designation as DESIGNATION, price_list2 as TGweb, prix_revient as PR, price_list1 as PV, format((( price_list1 - prix_revient)/( price_list1/100)), 2 ) as MARGE, tva_id as TVA, promotion as PROMO from articles where format((( price_list1 - prix_revient)/( price_list1/100)), 2 ) > '" + valeur + "' order by ref_code_barre";
        getResultat8Lines(requete);
    }

    private void getResultat2Lines(String requete) {
        dbr.open();
        try {
            ResultSet rs = dbr.getTable(requete);
            if (rs.next()) {
                resultatsRangees = new Vector();
                resultatsColonnes = new Vector();
                do {
                    resultatsRangees.addElement(rs.getString(1));
                    resultatsRangees.addElement(rs.getString(2));
                } while (rs.next());
                ResultSetMetaData rsmd = rs.getMetaData();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    resultatsColonnes.addElement(rsmd.getColumnName(i));
                }

                resultat = true;
            } else {
                resultat = false;
            }
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();
            resultat = false;
        }
        dbr.close();
    }

    private void getResultat8Lines(String requete) {
        dbr.open();
        try {
            ResultSet rs = dbr.getTable(requete);
            if (rs.next()) {
                resultatsRangees = new Vector();
                resultatsColonnes = new Vector();
                do {
                    resultatsRangees.addElement(rs.getString(1));
                    resultatsRangees.addElement(rs.getString(2));
                    resultatsRangees.addElement(rs.getString(3));
                    resultatsRangees.addElement(rs.getString(4));
                    resultatsRangees.addElement(rs.getString(5));
                    resultatsRangees.addElement(rs.getString(6));
                    resultatsRangees.addElement(rs.getString(7));
                    resultatsRangees.addElement(rs.getString(8));
                } while (rs.next());
                ResultSetMetaData rsmd = rs.getMetaData();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    resultatsColonnes.addElement(rsmd.getColumnName(i));
                }

                resultat = true;
            } else {
                resultat = false;
            }
        } catch (SQLException sqlex) {
            sqlex.printStackTrace();
            resultat = false;
        }
        dbr.close();
    }
    private OLD_RequestDB dbr;
    private Vector resultatsRangees;
    private Vector resultatsColonnes;
    public static final int REFERENCES = 1;
    public static final int DESIGNATIONS = 2;
    public static final int PROMOTIONS = 3;
    public static final int ALERTES = 4;
    public static final int FAMILLES_ID = 5;
    public static final int FOURNISSEURS_ID = 6;
    public static final int LIKE_REFERENCES = 8;
    public static final int LIKE_DESIGNATIONS = 9;
    public static final int LIKE_WEB = 10;
    public static final int LIKE_EMPLACEMENT = 11;
    public static final int PRIX_VENTE_PPQ = 12;
    public static final int PRIX_VENTE_PGQ = 13;
    public static final int MARGE_PPQ = 14;
    public static final int MARGE_PGQ = 15;
    public static final int FAMILLES = 16;
    public static final int FOURNISSEURS = 17;
    public static final int EMPLACEMENT = 18;
    public static final int WEB = 19;
    public static final int FICHE_D = 20;
    public static final int FICHE_LIKE_D = 21;
    public static final int FICHE_CA = 22;
    public static final int FICHE_LIKE_CA = 23;
    private boolean resultat;
}
